﻿/****** Object:  StoredProcedure [dbo].[OWU_MerchantCardOrderForm_Insert]    Script Date: 04/21/2010 16:55:49 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OWU_MerchantCardOrderForm_Insert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OWU_MerchantCardOrderForm_Insert]
GO
/****** Object:  StoredProcedure [dbo].[OWU_MerchantCardOrderForm_Insert]    Script Date: 04/21/2010 16:55:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Dale Hildebrand
-- Create date: 04/21/2010
-- Description:	Order a card for the provided 
--				merchant
-- =============================================
CREATE PROCEDURE [dbo].[OWU_MerchantCardOrderForm_Insert]
(
	@CreatedByUserID INT,
	@UserID INT,
	@FirstName NVARCHAR(30),
	@LastName NVARCHAR(30),
	@Address NVARCHAR(70),
	@City NVARCHAR(50),
	@StateProv NVARCHAR(40),
	@Country NVARCHAR(30),
	@ZipPostal NVARCHAR(10),
	@ValidFrom DATETIME,
	@ExpiryDate DATETIME
)
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO [dbo].[CardOrders]
           ([UserID]
           ,[FirstName]
           ,[LastName]
           ,[Address]
           ,[City]
           ,[StateProv]
           ,[Country]
           ,[ZipPostal]
           ,[ValidFrom]
           ,[Expiry]
           ,[IsPrinted])
     VALUES
           (@UserID
           ,@FirstName
           ,@LastName
           ,@Address
           ,@City
           ,@StateProv
           ,@Country
           ,@ZipPostal
           ,@ValidFrom
           ,@ExpiryDate
           ,'no')
           
    DECLARE @CardOrderedRoleID INT
    SET @CardOrderedRoleID = (
		SELECT TOP(1) RoleID
		FROM Roles
		WHERE RoleName = 'Card Ordered'
	)
	
	IF NOT EXISTS(    
		SELECT UserID
		FROM UserRoles
		WHERE UserID=@UserID
			AND RoleID=@CardOrderedRoleID
	)
	BEGIN
		INSERT INTO [dbo].[UserRoles]
				   ([UserID]
				   ,[RoleID]
				   ,[ExpiryDate]
				   ,[IsTrialUsed]
				   ,[EffectiveDate]
				   ,[CreatedByUserID]
				   ,[CreatedOnDate]
				   ,[LastModifiedByUserID]
				   ,[LastModifiedOnDate])
			 VALUES
				   (@UserID
				   ,@CardOrderedRoleID
				   ,NULL
				   ,1
				   ,NULL
				   ,@CreatedByUserID
				   ,GETDATE()
				   ,@CardOrderedRoleID
				   ,GETDATE())
	END
END
GO